package com.cmys.money.cloudshop.util.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 基于poi-3.0
 */
public class PoiExcelUtils {

	protected final static Log logger = LogFactory.getLog(PoiExcelUtils.class);

	/**
	 * POI导入Excel，导入的信息必须要与注释的列号对应
	 * 
	 * @param cls
	 * @param filePath
	 * @return
	 * @throws Exception
	 */
	public static List<Object> poiInport(Class<?> cls, String filePath) throws Exception {

		logger.debug("获得有注释的字段数组");
		Field[] fields = cls.getDeclaredFields();
		List<Field> fieldList = new ArrayList<Field>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(PoiExcelAnno.class)) {
				fieldList.add(field);
			}
		}
		logger.debug("有注解的字段数: " + fieldList.size());
		
		logger.info("读取Excel文件");
		InputStream is = new FileInputStream(filePath);
		POIFSFileSystem poifs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(poifs);
		
		int sheetCount = wb.getNumberOfSheets();
		logger.debug("读取Sheet数目: " + sheetCount);
		
		logger.info("读取第一个Sheet对象，序号从0开始");
		HSSFSheet sheet = wb.getSheetAt(0);
		
		int rowCount = sheet.getPhysicalNumberOfRows();
		logger.debug("读取Sheet对象的有效行数:" + rowCount);
		
		List<Object> list = new ArrayList<Object>();
		for (int rowNum = 1; rowNum < rowCount; rowNum++) {
			// 获得Row对象，序号从1开始，忽略标题行
			HSSFRow row = sheet.getRow(rowNum);
			Object bean = cls.newInstance();
			for (Field field : fieldList) {
				PoiExcelAnno anno = (PoiExcelAnno) field.getAnnotation(PoiExcelAnno.class);
				logger.debug(anno);
				String colName = anno.colName();
				int colNum = anno.colNum();
				String colType = anno.colType();
				// 获得Cell对象，序号从0开始
				HSSFCell cell = row.getCell((short) colNum);
				// TODO:先默认成都是String类型，根据单元格类型来选择对应单元格取值方法
				int cellType = cell.getCellType();
				String cellValue = cell.getStringCellValue();
				// 获得该属性对应的set方法
				String fieldName = field.getName();
				Class<?> fileTypeClass = field.getType();
				String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
				Method method = cls.getMethod(methodName, new Class[] { fileTypeClass });
				// TODO:还需利用fileTypeClass转换下，不一定是正确的对应类型
				Object filedValue = cellValue;
				// 用反射方法设值
				method.invoke(bean, filedValue);
			}
		}
		return list;
	}

	/**
	 * POI写入Excel，会根据注释的列号生成信息
	 * 
	 * @param list
	 * @param cls
	 * @param filePath
	 * @throws Exception
	 */
	public static void poiExport(List<Object> list, Class<?> cls, String filePath) throws Exception {

		// 获得有注释的属性数组
		Field[] fields = cls.getDeclaredFields();
		List<Field> fieldList = new ArrayList<Field>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(PoiExcelAnno.class)) {
				fieldList.add(field);
			}
		}
		// 创建Workbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建Sheet
		HSSFSheet sheet = wb.createSheet("Sheet1");
		// 创建标题行
		HSSFRow titleRow = sheet.createRow(0); // 创建行项
		for (Field field : fieldList) {
			PoiExcelAnno anno = (PoiExcelAnno) field.getAnnotation(PoiExcelAnno.class);
			int colNum = anno.colNum();
			String colName = anno.colName();
			HSSFCell cell = titleRow.createCell((short) colNum); // 创建单元格
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(colName);
		}
		// 创建数据行
		for (int rowNum = 1, i = 0, len = list.size(); i < len; rowNum++, i++) {
			Object bean = list.get(i);
			HSSFRow row = sheet.createRow(rowNum);
			for (Field field : fieldList) {
				PoiExcelAnno anno = (PoiExcelAnno) field.getAnnotation(PoiExcelAnno.class);
				int colNum = anno.colNum();
				// 获得该属性对应的get方法
				String fieldName = field.getName();
				String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
				Method method = cls.getMethod(methodName, new Class[] {}); // get方法没有声明变量
				Class<?> returnValueTypeClass = method.getReturnType(); // 返回值类型（更准确些）
				// 用反射方法取值
				Object value = method.invoke(bean, new Object[] {});
				// TODO: 根据返回值类型转化成对应类型的值
				HSSFCell cell = row.createCell((short) colNum); // 创建单元格
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				// TODO: 根据类型来选择设置方法设置单元格数据
				cell.setCellValue(value.toString());
			}
		}
		// 获得输出流
		FileOutputStream os = new FileOutputStream(filePath);
		// 生成Excel
		wb.write(os);
		os.close();
	}

	// TODO:
	// 根据Java类型取Poi对应类型
	// switch(cellType){
	// case 1: cell.getBooleanCellValue();break;
	// case 2:
	// }
	// cell.getBooleanCellValue();
	//
	// cell.getNumericCellValue();
	//
	// cell.getStringCellValue();
	//
	// cell.getDateCellValue();
	// 根据Poi类型取Java对应类型
	// 还有java自己的类型互转，这方面的基础知识

	// POI写入Excel
	// public static void poiExport(List<DynaBean> list, Class<?> cls,
	// OutputStream os) throws Exception{
	// // 获得有注释的属性数组
	// Field[] fields = cls.getDeclaredFields();
	// List<Field> fieldList = new ArrayList<Field>();
	// for(Field field : fields){
	// if(field.isAnnotationPresent(PoiExcelAnno.class)){
	// fieldList.add(field);
	// }
	// }
	// // 创建Workbook对象
	// HSSFWorkbook wb = new HSSFWorkbook();
	// // 创建Sheet
	// HSSFSheet sheet = wb.createSheet("Sheet1");
	// // 创建标题行
	// HSSFRow titleRow = sheet.createRow(0); // 创建行项
	// for(Field field : fieldList){
	// PoiExcelAnno anno =
	// (PoiExcelAnno)field.getAnnotation(PoiExcelAnno.class);
	// int colNum = anno.colNum();
	// String colName = anno.colName();
	// HSSFCell cell = titleRow.createCell((short)colNum); // 创建单元格
	// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
	// cell.setCellValue(colName);
	// }
	// // 创建数据行
	// for(int rowNum=1, i=0, len=list.size(); i<len; rowNum++, i++){
	// DynaBean bean = (DynaBean)list.get(i);
	// HSSFRow row = sheet.createRow(rowNum);
	// for(Field field : fieldList){
	// PoiExcelAnno anno =
	// (PoiExcelAnno)field.getAnnotation(PoiExcelAnno.class);
	// int colNum = anno.colNum();
	// // 获得该属性对应的get方法
	// String fieldName = field.getName();
	// Object value = bean.get(fieldName.toLowerCase());
	// HSSFCell cell = row.createCell((short)colNum); // 创建单元格
	// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
	// cell.setCellValue(value.toString());
	// }
	// }
	// // 生成Excel
	// wb.write(os);
	// os.flush();
	// os.close();
	// }

}
